package com.gistmap.ecepoi.common;

import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author zhangran
 * @date 2018/7/4
 */
@Component
@Slf4j
public class ExcelService<T> {

    /**
     * 先缓存拿，没有则反射
     * @param clz
     * @return
     */
    public List<ExcelHeader> getHeaderList(Class clz) {
        return ReflectCache.INSTANCE.get(clz.getName()) == null ?
                getHeaderListAndCache(clz) : ReflectCache.INSTANCE.get(clz.getName());
    }

    /**
     * 反射列名后缓存
     * @param clz
     * @return
     */
    public List<ExcelHeader> getHeaderListAndCache(Class clz){
        List<ExcelHeader> headers = Lists.newArrayList();
        Field[] fields = clz.getDeclaredFields();
        for (Field field : fields) {
            String name = field.getName();
            if (field.isAnnotationPresent(ExcelColumn.class)) {
                ExcelColumn column = field.getAnnotation(ExcelColumn.class);
                headers.add(new ExcelHeader(column.name(), column.order(), name));
            }
        }
        ReflectCache.INSTANCE.put(clz.getName(), headers);
        return headers;
    }

    public Workbook handleExcel(List objs, Class clz) {
        Workbook wb = new HSSFWorkbook();
        try {
            Sheet sheet = wb.createSheet();
            Row r = sheet.createRow(0);
            List<ExcelHeader> headers = getHeaderList(clz);
            Collections.sort(headers);
            // 写标题
            for (int i = 0; i < headers.size(); i++) {
                r.createCell(i).setCellValue(headers.get(i).getTitle());
            }
            // 写数据
            Object obj = null;
            for (int i = 0; i < objs.size(); i++) {
                r = sheet.createRow(i + 1);
                obj = objs.get(i);
                for (int j = 0; j < headers.size(); j++) {
                    r.createCell(j).setCellValue(BeanUtils.getProperty(obj, headers.get(j).getFieldName()));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    public Workbook handleExcel(String[] names){
        Workbook wb = new HSSFWorkbook();
        try {
            Sheet sheet = wb.createSheet();
            Row r = sheet.createRow(0);
            for (int i = 0; i < names.length; i++) {
                r.createCell(i).setCellValue(names[i]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    public List<T> toObject(String filePath, InputStream inputStream, Class clazz) throws IOException {

        List<T> objects = Lists.newArrayList();
        if (filePath == null || !isExcel(filePath)) {
            log.info("不是excel格式的文件");
        }
        Workbook hssfWorkbook = new HSSFWorkbook(inputStream);
        for (int num = 0; num < hssfWorkbook.getNumberOfSheets(); num++) {
            Sheet sheet = hssfWorkbook.getSheetAt(num);
            if (sheet == null) {
                continue;
            }
            Iterator rows = sheet.rowIterator();
            Row titleRow = (HSSFRow) rows.next();
            Map columnMap = generateColumnMap(titleRow);
            while (rows.hasNext()) {
                Row row = (HSSFRow) rows.next();
                T object = null;
                try {
                    object = (T) clazz.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    String columnName = getExcelName(field);
                    if (columnMap.get(columnName) == null) {
                        continue;
                    }
                    int columnNumber = (int) columnMap.get(columnName);
                    String value = getValueFromCell(row.getCell(columnNumber));
                    setFieldValue(field, object, value);
                }
                objects.add(object);
            }
        }
        return objects;
    }

    public static boolean isExcel(String filePath) {
        return filePath.matches("^.*\\.(?:xls)$")||filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 从标题行中解析出excel标题和列数的关系，返回标题名称->第几列的映射map
     * @param titleRow
     * @return
     */
    private Map generateColumnMap(Row titleRow) {
        Map columnMap = new HashMap();
        Iterator cells = titleRow.cellIterator();
        int columnNum = 0;
        while (cells.hasNext()) {
            Cell cell = (HSSFCell) cells.next();
            String cellValue = getValueFromCell(cell);
            if (!StringUtils.isEmpty(cellValue)) {
                columnMap.put(cellValue, columnNum);
            }
            columnNum++;
        }
        return columnMap;
    }

    /**
     * 从excel表格中获取值，并判断类型
     */
    private String getValueFromCell(Cell cell) {
        CellType cellType=null;
        if(cell!=null){
            cellType= cell.getCellTypeEnum();
        }
        if (cellType == CellType.NUMERIC) {
            String str = null;
            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                str = sdf.format(cell.getDateCellValue());
            } else {
                DecimalFormat format = new DecimalFormat("#");
                Number value = cell.getNumericCellValue();
                str = format.format(value);
            }
            return str;
        } else if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cellType == CellType.FORMULA) {
            return cell.getCellFormula();
        } else if (cellType == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else {
            return null;
        }
    }

    /**
     * 从Field 对象获取注解的值
     * @param field
     * @return
     */
    private String getExcelName(Field field) {
        ExcelColumn excelAnnotation = field.getAnnotation(ExcelColumn.class);
        if (excelAnnotation == null) {
            return null;
        }
        return excelAnnotation.name();
    }

    /**
     * 通过反射判断属性值，并自动判断属性数据类型
     * @param field
     * @param object
     * @param value
     */
    private void setFieldValue(Field field, Object object, String value) {
        field.setAccessible(true);
        try {
            String type = field.getType().getTypeName();
            if (type.equals("java.lang.String")) {
                field.set(object, value);
            } else if (type.equals("java.lang.Double")) {
                field.set(object, Double.valueOf(value));
            } else if (type.equals("java.lang.Integer")) {
                field.set(object, Integer.valueOf(value));
            } else if (type.equals("java.lang.Long")) {
                field.set(object, Long.valueOf(value));
            } else if (type.equals("java.lang.Byte")) {
                field.set(object, Byte.valueOf(value));
            } else if (type.equals("java.lang.Boolean")) {
                field.set(object, Boolean.valueOf(value));
            } else if (type.equals("java.lang.Float")) {
                field.set(object, Float.valueOf(value));
            } else if (type.equals("java.lang.Short")) {
                field.set(object, Short.valueOf(value));
            } else {
                field.set(object, null);
            }
        } catch (Exception e) {
            log.info(e.getMessage());
        }
    }
}
